AnalyticDB for MySQL企业版、基础版及湖仓版支持通过外表读取并导入外部数据。导入数据时,您可以选择常规导入和弹性导入两种方式。弹性导入相较于常规导入,可以大幅减少资源的消耗,降低导入过程中对在线读写业务的影响。本文介绍如何通过外表查询OSS数据,并将OSS的数据导入AnalyticDB for MySQL集群。
前提条件
AnalyticDB for MySQL集群的产品系列为企业版、基础版或湖仓版。
AnalyticDB for MySQL集群与OSS存储空间位于相同地域。
已将数据文件上传至OSS目录中。具体操作,请参见上传文件。
示例数据说明
本文示例将数据文件person
上传至OSS中的testBucketName/adb/dt=2023-06-15
目录,数据行分隔符为换行符,列分隔符为英文逗号(,)。person
中的示例数据如下:
1,james,10,2023-06-15
2,bond,20,2023-06-15
3,jack,30,2023-06-15
4,lucy,40,2023-06-15
操作步骤
进入SQL开发编辑器。
登录云原生数据仓库AnalyticDB MySQL控制台,在左上角选择集群所在地域。在左侧导航栏,单击集群列表,在企业版、基础版或湖仓版页签下,单击目标集群ID。
在左侧导航栏,单击 。
导入数据。
数据导入方式分为常规导入(默认)和弹性导入。常规导入在计算节点中读取源数据,然后在存储节点中构建索引,消耗计算资源和存储资源。弹性导入在Serverless Spark Job中读取源数据和构建索引,消耗Job型资源组的资源。仅内核版本3.1.10.0及以上且已创建Job型资源组的企业版、基础版及湖仓版集群支持弹性导入数据。更多内容,请参见数据导入方式介绍。
常规导入
创建外部数据库。
CREATE EXTERNAL DATABASE adb_external_db;
创建外表。使用CREATE EXTERNAL TABLE语句在外部数据库
adb_external_db
中创建OSS外表。本文以adb_external_db.person为例。说明AnalyticDB for MySQL外表的字段名称、字段数量、字段顺序、字段类型需要与和OSS文件相同。
创建OSS非分区外表
创建OSS分区外表
OSS外表的语法说明,请参见CREATE EXTERNAL TABLE。
查询数据。
数据表创建成功后,您可以在AnalyticDB for MySQL中通过SELECT语句查询OSS的数据。
SELECT * FROM adb_external_db.person;
返回结果如下:
+------+-------+------+-----------+ | id | name | age | dt | +------+-------+------+-----------+ | 1 | james | 10 |2023-06-15 | | 2 | bond | 20 |2023-06-15 | | 3 | jack | 30 |2023-06-15 | | 4 | lucy | 40 |2023-06-15 | +------+-------+------+-----------+ 4 rows in set (0.35 sec)
在AnalyticDB for MySQL中创建数据库。如果有已创建的数据库,可以忽略本步骤。示例如下:
CREATE DATABASE adb_demo;
在AnalyticDB for MySQL中创建表用于存储从OSS中导入的数据。示例如下:
说明创建的内表和步骤b中创建的外表的字段名称、字段数量、字段顺序、字段类型必须相同。
CREATE TABLE IF NOT EXISTS adb_demo.adb_import_test( id INT, name VARCHAR(1023), age INT, dt VARCHAR(1023) ) DISTRIBUTE BY HASH(id);
向表中导入数据。
方法一:使用
INSERT INTO
语句导入数据,当主键重复时会自动忽略当前写入数据,数据不做更新,作用等同于INSERT IGNORE INTO
,更多信息,请参见INSERT INTO。示例如下:INSERT INTO adb_demo.adb_import_test SELECT * FROM adb_external_db.person;
方法二:使用
INSERT OVERWRITE INTO
语句同步导入数据,会覆盖表中原有的数据。示例如下:INSERT OVERWRITE INTO adb_demo.adb_import_test SELECT * FROM adb_external_db.person;
方法三:使用
INSERT OVERWRITE INTO
语句异步导入数据,更多信息,请参见异步写入。示例如下:SUBMIT JOB INSERT OVERWRITE adb_demo.adb_import_test SELECT * FROM adb_external_db.person;
弹性导入
创建数据库。如果有已创建的数据库,可以忽略本步骤。示例如下:
CREATE DATABASE adb_demo;
创建外表。
说明AnalyticDB for MySQL外表的字段名称、字段数量、字段顺序、字段类型需要与和OSS文件相同。
弹性导入仅支持
CREATE TABLE
语句创建外表。
CREATE TABLE oss_import_test_external_table ( id INT(1023), name VARCHAR(1023), age INT, dt VARCHAR(1023) ) ENGINE='OSS' TABLE_PROPERTIES='{ "endpoint":"oss-cn-hangzhou-internal.aliyuncs.com", "url":"oss://<bucket-name>/adb/oss_import_test_data.csv", "accessid":"LTAI5t8sqJn5GhpBVtN8****", "accesskey":"HlClegbiV5mJjBYBJHEZQOnRF7****", "delimiter":"," }';
重要创建外表时,CSV、Parquet、ORC格式的外表支持设置的TABLE_PROPERTIES参数不同:
CSV格式:仅支持设置
endpoint
、url
、accessid
、accesskey
、format
、delimiter
、null_value
和partition_column
参数。Parquet格式:仅支持设置
endpoint
、url
、accessid
、accesskey
、format
和partition_column
参数。ORC格式:仅支持设置
endpoint
、url
、accessid
、accesskey
、format
和partition_column
参数。
查询数据。
数据表创建成功后,您可以在AnalyticDB for MySQL中通过SELECT语句查询OSS的数据。
SELECT * FROM oss_import_test_external_table;
返回结果如下:
+------+-------+------+-----------+ | id | name | age | dt | +------+-------+------+-----------+ | 1 | james | 10 |2023-06-15 | | 2 | bond | 20 |2023-06-15 | | 3 | jack | 30 |2023-06-15 | | 4 | lucy | 40 |2023-06-15 | +------+-------+------+-----------+ 4 rows in set (0.35 sec)
在AnalyticDB for MySQL中创建表用于存储从OSS中导入的数据。示例如下:
说明创建的内表和步骤b中创建的外表的字段名称、字段数量、字段顺序、字段类型必须相同。
CREATE TABLE adb_import_test ( id INT, name VARCHAR(1023), age INT, dt VARCHAR(1023) ) DISTRIBUTED BY HASH(id);
导入数据。
重要弹性导入仅支持通过
INSERT OVERWRITE INTO
语句导入数据。方法一:执行INSERT OVERWRITE INTO弹性导入数据,会覆盖表中原有的数据。示例如下:
/+*elastic_load=true, elastic_load_configs=[adb.load.resource.group.name=resource_group]*/ INSERT OVERWRITE INTO adb_demo.adb_import_test SELECT * FROM adb_demo.oss_import_test_external_table;
方法二:异步执行INSERT OVERWRITE INTO弹性导入数据。通常使用
SUBMIT JOB
提交异步任务,由后台调度。/*+elastic_load=true, elastic_load_configs=[adb.load.resource.group.name=resource_group]*/ SUBMIT JOB INSERT OVERWRITE INTO adb_demo.adb_import_test SELECT * FROM adb_demo.oss_import_test_external_table;
重要异步提交弹性导入任务时,不支持设置优先级队列。
返回结果如下:
+---------------------------------------+ | job_id | +---------------------------------------+ | 2023081517195102101701907203151****** |
使用
SUBMIT JOB
提交异步任务后,返回结果仅表示异步任务提交成功。您可以通过job_id终止异步任务或查询异步任务状态,判断任务是否执行成功。具体操作,请参见异步提交导入任务。Hint参数说明:
elastic_load:是否使用弹性导入方式。取值:true或false(默认值)。
elastic_load_configs:弹性导入方式支持配置的参数。参数需使用方括号([ ])括起来,且多个参数之间以竖线(|)分隔,支持配置的参数如下表所示:
参数
是否必填
说明
adb.load.resource.group.name
是
执行弹性导入任务的Job资源组名称。
adb.load.job.max.acu
否
单个弹性导入任务最多使用的资源。单位为ACU,最小值为5 ACU。默认值为集群Shard个数+1。
执行如下语句可查询集群Shard个数:
SELECT count(1) FROM information_schema.kepler_meta_shards;
spark.driver.resourceSpec
否
Spark driver的资源规格。默认值为small。取值范围,请参见Spark资源规格列表的型号列。
spark.executor.resourceSpec
否
Spark executor的资源规格。默认值为large。取值范围,请参见Spark资源规格列表的型号列。
spark.adb.executorDiskSize
否
Spark executor的磁盘容量,取值范围为(0,100],单位为GiB,默认值为10 Gi。更多信息,请参见指定Driver和Executor资源。
(可选)查看已提交的导入任务是否为弹性导入任务。
SELECT job_name, (job_type = 3) AS is_elastic_load FROM INFORMATION_SCHEMA.kepler_meta_async_jobs where job_name = "2023081818010602101701907303151******";
返回结果如下:
+---------------------------------------+------------------+ | job_name | is_elastic_load | +---------------------------------------+------------------+ | 2023081517195102101701907203151****** | 1 | +---------------------------------------+------------------+
is_elastic_load
的返回值为1,表示已提交的导入任务是弹性导入任务;若为0,则表示已提交的导入任务是常规导入任务。